package com.mcxx.util;


import com.mcxx.modules.temporary.order.dto.TmpOrderStatement;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.OutputStream;


public class POIWriterExecl {


    public static XSSFWorkbook createOrderDetailExeclFile(TmpOrderStatement orderStatement) throws Exception {

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet = wb.createSheet("sheet1");
        sheet.setDefaultColumnWidth(11);
        sheet.setDefaultRowHeight((short)400);

        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 8));
        Row row2 = sheet.createRow(2);
        row2.setHeight((short)700);
        CellStyle cellStyle = getStyle(wb);
        CellStyle boldCellStyle = getBoldStyle(wb);

        Row row3 = sheet.createRow(3);
        row3.setHeight((short)350);
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 7, 8));
        Cell cell = row3.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("结算单位：");
        cell = row3.createCell(1);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(orderStatement.getArea());
        cell = row3.createCell(3);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("救助年月：");
        cell = row3.createCell(4);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(orderStatement.getStartMonth());
        cell = row3.createCell(6);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("救助日期：");
        cell = row3.createCell(7);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(orderStatement.getCommitDate());

        Row row4 = sheet.createRow(4);
        row4.setHeight((short)550);
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 7, 8));
        cell = row4.createCell(0);
        cell.setCellValue("姓名");
        cell = row4.createCell(1);
        cell.setCellValue(orderStatement.getApplyName());
        cell = row4.createCell(3);
        cell.setCellValue("身份证号");
        cell = row4.createCell(4);
        cell.setCellValue(orderStatement.getApplyIdcard());
        cell = row4.createCell(6);
        cell.setCellValue("性别");
        cell = row4.createCell(7);
        cell.setCellValue(orderStatement.getSex());

        Row row5 = sheet.createRow(5);
        row5.setHeight((short)550);
        sheet.addMergedRegion(new CellRangeAddress(5, 5, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(5, 5, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(5, 5, 7, 8));
        cell = row5.createCell(0);
        cell.setCellValue("出生日期");
        cell = row5.createCell(1);
        cell.setCellValue(orderStatement.getBirthday());
        cell = row5.createCell(3);
        cell.setCellValue("户口类型");
        cell = row5.createCell(4);
        cell.setCellValue(orderStatement.getCensusType());
        cell = row5.createCell(6);
        cell.setCellValue("对象类别");
        cell = row5.createCell(7);
        cell.setCellValue(orderStatement.getObjectType());

        Row row6 = sheet.createRow(6);
        row6.setHeight((short)550);
        sheet.addMergedRegion(new CellRangeAddress(6, 6, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(6, 6, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(6, 6, 7, 8));
        cell = row6.createCell(0);
        cell.setCellValue("银行账号");
        cell = row6.createCell(1);
        cell.setCellValue(orderStatement.getAccount());
        cell = row6.createCell(3);
        cell.setCellValue("户主姓名");
        cell = row6.createCell(4);
        cell.setCellValue(orderStatement.getName());
        cell = row6.createCell(6);
        cell.setCellValue("户主身份证");
        cell = row6.createCell(7);
        cell.setCellValue(orderStatement.getIdcard());

        Row row7 = sheet.createRow(7);
        row7.setHeight((short)550);
        sheet.addMergedRegion(new CellRangeAddress(7, 7, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(7, 7, 4, 8));
        cell = row7.createCell(0);
        cell.setCellValue("联系电话");
        cell = row7.createCell(1);
        cell.setCellValue(orderStatement.getPhone());
        cell = row7.createCell(3);
        cell.setCellValue("地址");
        cell = row7.createCell(4);
        cell.setCellValue(orderStatement.getAddress());

        Row row8 = sheet.createRow(8);
        row8.setHeight((short)550);
        sheet.addMergedRegion(new CellRangeAddress(8, 8, 1, 8));
        cell = row8.createCell(0);
        cell.setCellValue("备注");
        cell = row8.createCell(1);
        cell.setCellValue(orderStatement.getRemark());

        Row row9 = sheet.createRow(9);
        row9.setHeight((short)550);
        sheet.addMergedRegion(new CellRangeAddress(9, 9, 1, 2));
        sheet.addMergedRegion(new CellRangeAddress(9, 9, 4, 8));
        cell = row9.createCell(0);
        cell.setCellValue("救助类型");
        cell = row9.createCell(1);
        cell.setCellValue(orderStatement.getTempSaveType());
        cell = row9.createCell(3);
        cell.setCellValue("救助金额");
        cell = row9.createCell(4);
        cell.setCellValue(orderStatement.getSalvMoney());

        Row row10 = sheet.createRow(10);
        row10.setHeight((short)300);
        sheet.addMergedRegion(new CellRangeAddress(10, 10, 2, 3));
        cell = row10.createCell(0);
        cell.setCellValue("结算人：");
        cell.setCellStyle(cellStyle);
        cell = row10.createCell(2);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("单位审批人签字：");
        cell = row10.createCell(6);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("对象签字：");

        for(int i=4;i<10;i++){
            Row row = sheet.getRow(i);
            for(int j=0;j<=8;j++){
                Cell scell = row.getCell(j);
                if(scell ==null){
                    row.createCell(j).setCellStyle(boldCellStyle);
                }else{
                    row.getCell(j).setCellStyle(boldCellStyle);
                }

            }
        }
        return wb;
    }



    public static CellStyle getStyle(Workbook workbook) {

        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setFontName("宋体");
        style.setFont(headerFont);
        style.setWrapText(true);
        return style;
    }


    public static CellStyle getBoldStyle(Workbook workbook) {

        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontName("宋体");
        style.setFont(headerFont);
        style.setWrapText(true);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        return style;
    }
}
